BEGIN TRANSACTION
GO
ALTER TABLE dbo.Voucher ADD
	FatherName varchar(100) NULL
GO
COMMIT

--------------------------------------------------------------------------------------------------

USE [BS2]
GO
/****** Object:  View [dbo].[VW_Vouchers]    Script Date: 07/25/2019 13:37:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*---------------------------------------------------------------------------*/
ALTER VIEW [dbo].[VW_Vouchers]
AS
SELECT     dbo.VoucherDetails.VoucherID, dbo.Voucher.VoucherDate, dbo.Voucher.VoucherNo, ISNULL(dbo.Voucher.SVoucherNo, 0) AS SVoucherNo, dbo.Voucher.SessionID, 
                      dbo.SessionInfo.SessionTitle, dbo.SessionInfo.SessionFrom, dbo.SessionInfo.SessionTo, dbo.SessionInfo.CurrentSession, dbo.Voucher.Remarks AS MasterRemarks, 
                      dbo.Voucher.ReadOnly, dbo.VoucherDetails.VoucherDetailID, dbo.VoucherDetails.AccountNo, Accounts_4.AccountTitle, Accounts_4.AccountTitleUrdu, 
                      dbo.VoucherDetails.Remarks, CASE WHEN (dbo.VoucherDetails.DrOrCr = N'Dr') 
                      THEN dbo.VoucherDetails.Amount ELSE - 1 * dbo.VoucherDetails.Amount END AS Amount, CASE WHEN (dbo.VoucherDetails.DrOrCr = N'Dr') 
                      THEN dbo.VoucherDetails.Amount ELSE 0 END AS Debit, CASE WHEN (dbo.VoucherDetails.DrOrCr = N'Cr') THEN dbo.VoucherDetails.Amount ELSE 0 END AS Credit, 
                      dbo.VoucherDetails.DrOrCr, Accounts_4.ParentAccount, dbo.Voucher.BankAccountID, Accounts_1.AccountTitle AS BankAccountTitle, 
                      Accounts_1.AccountTitleUrdu AS BankAccountTitleUrdu, Accounts_1.Remarks AS BankAccountAddress, 
                      Accounts_1.CellNo + '  ' + Accounts_1.Phone AS BankAccountContactNo, Accounts_1.LiscNo, Accounts_1.LiscDate, dbo.Voucher.SaleTax, dbo.Voucher.Expense, 
                      dbo.Voucher.Discount, dbo.Voucher.InvDiscPercent, dbo.Voucher.InvDiscount, dbo.Voucher.Cash, dbo.Voucher.AddLess, dbo.Voucher.PrvBalance, 
                      dbo.Voucher.LoginID, dbo.Employees.EmployeeName, dbo.Voucher.HostName, dbo.Voucher.EntryDateTime, dbo.VoucherDetails.Amount AS ActualAmount, 
                      dbo.Voucher.ModifyID, Employees_1.EmployeeName AS ModifyName, dbo.Voucher.ModifyHostName, dbo.Voucher.ModifyDateTime, dbo.Voucher.CustomerName, 
                      dbo.vw_Items.GroupID, dbo.vw_Items.GroupCode, dbo.vw_Items.GroupName, dbo.vw_Items.GroupNameUrdu, dbo.vw_Items.Type, dbo.VoucherDetails.ItemID, 
                      dbo.vw_Items.ItemCode, dbo.vw_Items.ItemName, dbo.vw_Items.ItemNameUrdu, dbo.vw_Items.InUnit, dbo.vw_Items.PRate, dbo.vw_Items.SRate, 
                      dbo.vw_Items.CompanyID, dbo.vw_Items.CompanyCode, dbo.vw_Items.CompanyName, dbo.vw_Items.CompanyNameUrdu, dbo.vw_Items.RackID, 
                      dbo.vw_Items.RackCode, dbo.vw_Items.RackName, dbo.vw_Items.RackNameUrdu, dbo.VoucherDetails.BatchNo, dbo.VoucherDetails.ExpiryDate, 
                      dbo.VoucherDetails.Packs, dbo.VoucherDetails.Lose, dbo.VoucherDetails.PSize, dbo.VoucherDetails.Qty, dbo.VoucherDetails.Bonus, dbo.VoucherDetails.PackRate, 
                      dbo.VoucherDetails.Rate, dbo.VoucherDetails.STaxPercent, dbo.VoucherDetails.STaxAmount, dbo.VoucherDetails.DiscPercent, dbo.VoucherDetails.DiscAmount, 
                      dbo.VoucherDetails.NetAmount, dbo.Voucher.BookedByID, Accounts_2.AccountTitle AS BookedByName, Accounts_2.AccountTitleUrdu AS BookedByNameUrdu, 
                      Accounts_2.CellNo AS BookedByCellNo, Accounts_2.Phone AS BookedByPhone, Accounts_2.Remarks AS BookedByAddress, 
                      Accounts_2.ComPercent AS BookedByComPercent, dbo.Voucher.DeliveredByID, Accounts_3.AccountTitle AS DeliveredByName, 
                      Accounts_3.AccountTitleUrdu AS DeliveredByNameUrdu, Accounts_3.CellNo AS DeliveredByCellNo, Accounts_3.Phone AS DeliveredByPhone, 
                      Accounts_3.Remarks AS DeliveredByAddress, Accounts_3.ComPercent AS DeliveredByComPercent, Accounts_1.AreaID, dbo.Area.AreaCode, dbo.Area.AreaName, 
                      dbo.Area.AreaNameUrdu, dbo.Area.RegionID, dbo.Region.RegionCode, dbo.Region.RegionName, dbo.Region.RegionNameUrdu, dbo.Voucher.Amount AS AmountM, 
                      dbo.Voucher.TotalAmount AS TotalAmountM, dbo.Voucher.NetAmount AS NetAmountM, dbo.Voucher.BillNo, dbo.Voucher.TransID, 
                      dbo.Accounts.AccountTitle AS TransName, dbo.Accounts.AccountTitleUrdu AS TransNameUrdu, dbo.Accounts.Remarks AS TransAddress, 
                      dbo.Accounts.CellNo AS TransCellNo, dbo.Accounts.Phone AS TransPhone, dbo.Voucher.FatherName
FROM         dbo.Accounts AS Accounts_1 LEFT OUTER JOIN
                      dbo.Region RIGHT OUTER JOIN
                      dbo.Area ON dbo.Region.RegionID = dbo.Area.RegionID ON Accounts_1.AreaID = dbo.Area.AreaID RIGHT OUTER JOIN
                      dbo.Accounts AS Accounts_3 RIGHT OUTER JOIN
                      dbo.Accounts RIGHT OUTER JOIN
                      dbo.Voucher ON dbo.Accounts.AccountNo = dbo.Voucher.TransID ON Accounts_3.AccountNo = dbo.Voucher.DeliveredByID LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_2 ON dbo.Voucher.BookedByID = Accounts_2.AccountNo ON Accounts_1.AccountNo = dbo.Voucher.BankAccountID LEFT OUTER JOIN
                      dbo.Employees AS Employees_1 ON dbo.Voucher.ModifyID = Employees_1.EmployeeID LEFT OUTER JOIN
                      dbo.Employees ON dbo.Voucher.LoginID = dbo.Employees.EmployeeID RIGHT OUTER JOIN
                      dbo.vw_Items RIGHT OUTER JOIN
                      dbo.VoucherDetails ON dbo.vw_Items.ItemID = dbo.VoucherDetails.ItemID LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_4 ON dbo.VoucherDetails.AccountNo = Accounts_4.AccountNo ON dbo.Voucher.VoucherID = dbo.VoucherDetails.VoucherID LEFT OUTER JOIN
                      dbo.SessionInfo ON dbo.Voucher.SessionID = dbo.SessionInfo.SessionID

--------------------------------------------------------------------------------------------------